package com.dp.appi.dao.impl;

import com.dp.appi.dao.FootprintDao;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * Created by bchen on 2016/5/26.
 */

@Component
public class FootprintDaoImpl extends BaseDaoImpl implements FootprintDao {

    /**
     * 按年统计项目数和文章发布数
     *
     * @param user_id
     * @param year
     * @return
     */
    @Override
    public Map<String, Object> stageNumByYear(String user_id, int year) {
        StringBuffer sql = new StringBuffer();
        sql.append("select * from( ");
        sql.append("select "+year+" x,count(1) y_stage from p_stage a ");
        sql.append("where a.deleted = 0 ");
        sql.append("and (a.manager like '%"+user_id+"%' ");
        sql.append("or a.explorationer like '%"+user_id+"%' ");
        sql.append("or a.designer like '%"+user_id+"%' ");
        sql.append("or a.auditor like '%"+user_id+"%') ");
        sql.append("and ? >= date_format(a.create_time,'%Y') ");
        sql.append("and (? <= date_format(a.finish_time,'%Y') or a.finish_time is null) ");
        sql.append(") t1,( ");
        sql.append("select count(1) y_publish from e_encyclopedia ");
        sql.append("where author = ? ");
        sql.append("and ? = date_format(create_time,'%Y')) t2 ");
        return queryForMap(sql.toString(),year,year,user_id,year);
    }

    /**
     * 月份参与项目
     *
     * @param user_id
     * @param year
     * @return
     */
    @Override
    public List<Map<String, Object>> involList(String user_id, String year) {
        StringBuffer sql = new StringBuffer();
        sql.append("select date_format(a.create_time,'%Y-%m') mon1, ");
        sql.append("IFNULL(date_format(a.finish_time,'%Y-%m'),'"+year+"-12') mon2, ");
        sql.append("a.name p_name,b.thumb p_thumb,b.project_type p_type,date_format(a.create_time,'%Y-%m-%d %H:%i:%s') create_time, ");
        sql.append("CONCAT(IF(LOCATE(?,a.manager)=0,'','负责 '), ");
        sql.append("IF(LOCATE(?,a.explorationer)=0,'','踏勘 '), ");
        sql.append("IF(LOCATE(?,a.designer)=0,'','设计 '), ");
        sql.append("IF(LOCATE(?,a.auditor)=0,'','审核')) in_work ");
        sql.append("from p_stage a ");
        sql.append("LEFT JOIN p_baseinfo b on a.project_id = b.project_id ");
        sql.append("where a.deleted = 0 ");
        sql.append("and (a.manager like '%"+user_id+"%' ");
        sql.append("or a.explorationer like '%"+user_id+"%' ");
        sql.append("or a.designer like '%"+user_id+"%' ");
        sql.append("or a.auditor like '%"+user_id+"%') ");
        sql.append("and ? >= date_format(a.create_time,'%Y') ");
        sql.append("and (? <= date_format(a.finish_time,'%Y') or a.finish_time is null) ");
        sql.append("AND ? <= date_format(NOW(), '%Y') ");
        sql.append("ORDER BY date_format(a.finish_time,'%m') ; ");
        return getJdbcTemplate().queryForList(sql.toString(),user_id,user_id,user_id,user_id,year,year,year);
    }

    /**
     * 月份完成项目
     *
     * @param user_id
     * @param year
     * @return
     */
    @Override
    public List<Map<String, Object>> finishList(String user_id, String year) {
        StringBuffer sql = new StringBuffer();
        sql.append("select date_format(a.finish_time,'%Y-%m') mon,a.name p_name, ");
        sql.append("b.thumb p_thumb,b.project_type p_type,date_format(a.create_time,'%Y-%m-%d %H:%i:%s') create_time, ");
        sql.append("CONCAT(IF(LOCATE(?,a.manager)=0,'','负责 '), ");
        sql.append("IF(LOCATE(?,a.explorationer)=0,'','踏勘 '), ");
        sql.append("IF(LOCATE(?,a.designer)=0,'','设计 '), ");
        sql.append("IF(LOCATE(?,a.auditor)=0,'','审核')) in_work ");
        sql.append("from p_stage a ");
        sql.append("LEFT JOIN p_baseinfo b on a.project_id = b.project_id ");
        sql.append("where a.deleted = 0 ");
        sql.append("and (a.manager like '%"+user_id+"%' ");
        sql.append("or a.explorationer like '%"+user_id+"%' ");
        sql.append("or a.designer like '%"+user_id+"%' ");
        sql.append("or a.auditor like '%"+user_id+"%') ");
        sql.append("and ? = date_format(a.finish_time,'%Y') ");
        sql.append("ORDER BY date_format(a.finish_time,'%m') ");
        return getJdbcTemplate().queryForList(sql.toString(),user_id,user_id,user_id,user_id,year);
    }

    /**
     * 月份发布文章数
     *
     * @param user_id
     * @param year
     * @return
     */
    @Override
    public List<Map<String, Object>> publishList(String user_id, String year) {
        StringBuffer sql = new StringBuffer();
        sql.append("select date_format(a.create_time,'%Y-%m') mon,a.title,a.article_thumb,date_format(a.create_time,'%Y-%m-%d %H:%i:%s') create_time ");
        sql.append("from e_encyclopedia a ");
        sql.append("where author = ? ");
        sql.append("and ? = date_format(a.create_time,'%Y') ");
        sql.append("ORDER BY date_format(a.create_time,'%m') ");
        return getJdbcTemplate().queryForList(sql.toString(),user_id,year);
    }

    /**
     * 工作统计
     *
     * @param user_id
     * @return
     */
    @Override
    public Map<String, ?> workStatistics(String user_id) {
        StringBuffer sql = new StringBuffer();
        sql.append("select exp_mil/1000 exp_mil,exp_num,design_num,audit_num,manang_num  ");
        sql.append("from t_s_user ");
        sql.append("where id = ?");
        return queryForMap(sql.toString(),user_id);
    }

}
